import base64
import datetime
import json
import zlib
import pymysql
import secrets
import string
import requests
import io
from flask import Flask, render_template, jsonify
from flask import request, Response
from flask_bootstrap import Bootstrap
from openpyxl import Workbook
from openpyxl.styles import Font
import config

app = Flask(__name__)
app.secret_key = 'xiaowutu'
Bootstrap(app)

MAX_HISTORY = 233

HEADERS = {}
if config.CORE_API_TOKEN:
    HEADERS["Authorization"] = config.CORE_API_TOKEN


def get_db_connection():
    return pymysql.connect(
        host=config.DB_HOST,
        user=config.DB_USER,
        password=config.DB_PASSWORD,
        database=config.DB_NAME,
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )


def init_db_tables():
    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # 创建授权码表
            cursor.execute("""
                        CREATE TABLE IF NOT EXISTS auth_codes (
                            id INT AUTO_INCREMENT PRIMARY KEY,
                            code VARCHAR(33) NOT NULL UNIQUE,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            used BOOLEAN DEFAULT FALSE,
                            used_by_ip VARCHAR(45),
                            used_at DATETIME
                        )
                        """)
            # 创建翻译记录表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS translation_records (
                id INT AUTO_INCREMENT PRIMARY KEY,
                ip VARCHAR(45) NOT NULL,
                source_text TEXT NOT NULL,
                translated_text TEXT NOT NULL,
                source_lang VARCHAR(10) NOT NULL,
                target_lang VARCHAR(10) NOT NULL,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
            )
            """)

            # 创建访问限制表
            cursor.execute("""
            CREATE TABLE IF NOT EXISTS ip_access_limits (
                ip VARCHAR(45) PRIMARY KEY,
                count INT DEFAULT 0,
                last_access DATETIME
            )
            """)
        connection.commit()
    except Exception as e:
        print(f"Database initialization error: {str(e)}")
    finally:
        connection.close()


init_db_tables()

def compress_data(data):
    """压缩数据以减少存储空间"""
    json_str = json.dumps(data).encode('utf-8')
    compressed = zlib.compress(json_str)
    return base64.b64encode(compressed).decode('utf-8')


def decompress_data(compressed_str):
    """解压缩数据"""
    try:
        compressed = base64.b64decode(compressed_str.encode('utf-8'))
        json_str = zlib.decompress(compressed)
        return json.loads(json_str.decode('utf-8'))
    except:
        return []


@app.route("/")
def index():
    # 获取模型列表
    try:
        r = requests.get(f"{config.MTRAN_HOST}/models", headers=HEADERS, timeout=3)
        models = r.json().get("models", [])
    except Exception:
        models = []

    return render_template("index.html", models=models)


@app.route("/translate", methods=["POST"])
def translate():
    data = request.form
    source = data.get("source_lang")
    target = data.get("target_lang")
    text = data.get("text")

    # 获取客户端IP
    ip = request.headers.get('X-Forwarded-For', request.remote_addr)
    if ',' in ip:
        ip = ip.split(',')[0].strip()

    # 检查访问限制
    if not can_translate(ip):
        return jsonify({
            "error": "访问限制：每23小时只能翻译33次",
            "translation": ""
        }), 429

    try:
        r = requests.post(
            f"{config.MTRAN_HOST}/translate",
            json={"from": source, "to": target, "text": text},
            headers=HEADERS,
            timeout=23,
        )
        r.raise_for_status()  # 检查HTTP错误
        result = r.json().get("result", "翻译失败")
    except requests.exceptions.Timeout:
        result = "请求超时：服务器响应时间过长（如非服务器错误，请适当缩减文本）"
    except requests.exceptions.ConnectionError:
        result = "连接失败：无法连接到翻译服务器"
    except requests.exceptions.HTTPError as e:
        result = f"HTTP错误：{str(e)}"
    except Exception as e:
        result = f"请求失败: {e}"

    # 记录翻译请求
    record_translation(ip, source, target, text, result)

    # 返回翻译结果（历史记录现在由前端管理）
    return jsonify({
        "translation": result,
        "timestamp": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    })


def can_translate(ip):
    """检查IP是否可以继续翻译"""
    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # 检查IP是否存在记录
            cursor.execute(
                "SELECT * FROM ip_access_limits WHERE ip = %s",
                (ip,)
            )
            record = cursor.fetchone()

            now = datetime.datetime.now()

            if not record:
                # 新IP，创建记录
                cursor.execute(
                    "INSERT INTO ip_access_limits (ip, count, last_access) VALUES (%s, 1, %s)",
                    (ip, now)
                )
                return True

            # 检查时间间隔
            last_access = record['last_access']
            time_diff = now - last_access
            hours_diff = time_diff.total_seconds() / 3600

            if hours_diff >= 23:
                # 超过23小时，重置计数
                cursor.execute(
                    "UPDATE ip_access_limits SET count = 1, last_access = %s WHERE ip = %s",
                    (now, ip)
                )
                return True

            # 检查翻译次数
            if record['count'] >= 33:
                return False

            # 增加计数
            cursor.execute(
                "UPDATE ip_access_limits SET count = count + 1, last_access = %s WHERE ip = %s",
                (now, ip)
            )
            return True
    except Exception as e:
        print(f"Access limit check error: {str(e)}")
        return True  # 出错时允许访问
    finally:
        connection.commit()
        connection.close()


def record_translation(ip, source_lang, target_lang, source_text, translated_text):
    """记录翻译请求到数据库"""
    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            cursor.execute(
                """
                INSERT INTO translation_records 
                (ip, source_text, translated_text, source_lang, target_lang)
                VALUES (%s, %s, %s, %s, %s)
                """,
                (ip, source_text, translated_text, source_lang, target_lang)
            )
        connection.commit()
    except Exception as e:
        print(f"Translation record error: {str(e)}")
    finally:
        connection.close()


@app.route("/admin")
def admin_page():
    return render_template("admin.html")


@app.route("/admin/translation_records")
def translation_records():
    if not request.args.get('token') or request.args.get('token') != config.ADMIN_TOKEN:
        return "Unauthorized", 401

    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # 获取所有翻译记录
            cursor.execute("SELECT * FROM translation_records ORDER BY timestamp DESC")
            records = cursor.fetchall()

            # 获取访问限制信息
            cursor.execute("SELECT * FROM ip_access_limits ORDER BY last_access DESC")
            limits = cursor.fetchall()

            return jsonify({
                "translation_records": records,
                "access_limits": limits
            })
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        connection.close()


@app.route("/status")
def status():
    try:
        requests.get(f"{config.MTRAN_HOST}/health", timeout=2)
        return jsonify({"status": "🟢 在线"})
    except:
        return jsonify({"status": "🔴 离线"})


@app.route("/admin/generate_code", methods=["POST"])
def generate_auth_code():
    token = request.args.get('token')
    if not token or token != config.ADMIN_TOKEN:
        return "Unauthorized", 401

    # 生成33位随机授权码
    code = ''.join(secrets.choice(string.ascii_uppercase + string.digits) for _ in range(33))

    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            cursor.execute(
                "INSERT INTO auth_codes (code) VALUES (%s)",
                (code,)
            )
        connection.commit()
        return jsonify({"code": code})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        connection.close()


@app.route("/redeem_code", methods=["POST"])
def redeem_auth_code():
    data = request.get_json()
    code = data.get("code")
    ip = request.headers.get('X-Forwarded-For', request.remote_addr)
    if ',' in ip:
        ip = ip.split(',')[0].strip()

    if not code:
        return jsonify({"success": False, "message": "授权码不能为空"}), 400

    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # 检查授权码是否存在且未使用
            cursor.execute(
                "SELECT * FROM auth_codes WHERE code = %s AND used = FALSE AND DATE(created_at) = CURDATE()",
                (code,)
            )
            auth_code = cursor.fetchone()

            if not auth_code:
                return jsonify({"success": False, "message": "授权码无效或已过期"}), 400

            # 标记为已使用
            cursor.execute(
                "UPDATE auth_codes SET used = TRUE, used_by_ip = %s, used_at = NOW() WHERE id = %s",
                (ip, auth_code['id'])
            )

            # 重置IP限制
            cursor.execute(
                "UPDATE ip_access_limits SET count = 0 WHERE ip = %s",
                (ip,)
            )

        connection.commit()
        return jsonify({"success": True, "message": "授权码已兑换，限制已解除"})
    except Exception as e:
        return jsonify({"success": False, "message": str(e)}), 500
    finally:
        connection.close()


@app.route("/admin/auth_codes")
def auth_codes():
    token = request.args.get('token')
    if not token or token != config.ADMIN_TOKEN:
        return "Unauthorized", 401

    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # 获取所有授权码
            cursor.execute("""
                SELECT code, created_at, used, used_by_ip, used_at 
                FROM auth_codes 
                ORDER BY created_at DESC
            """)
            codes = cursor.fetchall()
        return jsonify({"auth_codes": codes})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        connection.close()


@app.route("/admin/export_auth_codes")
def export_auth_codes():
    """导出授权码为Excel文件"""
    # 验证管理员token
    if not request.args.get('token') or request.args.get('token') != config.ADMIN_TOKEN:
        return "Unauthorized", 401

    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # 获取所有授权码
            cursor.execute("""
                SELECT code, created_at, used, used_by_ip, used_at 
                FROM auth_codes 
                ORDER BY created_at DESC
            """)
            codes = cursor.fetchall()
        
        if not codes:
            return jsonify({"error": "没有授权码可导出"}), 404

        # 创建Excel工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = "授权码"
        
        # 设置表头
        headers = ['授权码', '创建时间', '状态', '使用IP', '使用时间']
        ws.append(headers)
        
        # 设置表头样式
        bold_font = Font(bold=True)
        for cell in ws[1]:
            cell.font = bold_font
        
        # 写入数据行
        for code in codes:
            status = "已使用" if code['used'] else "未使用"
            ws.append([
                code['code'],
                code['created_at'].strftime('%Y-%m-%d %H:%M:%S'),
                status,
                code['used_by_ip'] or '-',
                code['used_at'].strftime('%Y-%m-%d %H:%M:%S') if code['used_at'] else '-'
            ])
        
        # 调整列宽
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = (max_length + 2)
            ws.column_dimensions[column].width = adjusted_width
        
        # 创建内存中的Excel文件
        output = io.BytesIO()
        wb.save(output)
        output.seek(0)
        
        # 准备响应
        response = Response(
            output,
            mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response.headers["Content-Disposition"] = "attachment; filename=auth_codes.xlsx"
        return response
        
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        connection.close()


@app.route("/admin/export_translation_records")
def export_translation_records():
    """导出翻译记录为Excel文件"""
    # 验证管理员token
    if not request.args.get('token') or request.args.get('token') != config.ADMIN_TOKEN:
        return "Unauthorized", 401

    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # 获取所有翻译记录
            cursor.execute("SELECT * FROM translation_records ORDER BY timestamp DESC")
            records = cursor.fetchall()
        
        if not records:
            return jsonify({"error": "没有翻译记录可导出"}), 404

        # 创建Excel工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = "翻译记录"
        
        # 设置表头
        headers = ['ID', 'IP地址', '源文本', '翻译文本', '源语言', '目标语言', '时间戳']
        ws.append(headers)
        
        # 设置表头样式
        bold_font = Font(bold=True)
        for cell in ws[1]:
            cell.font = bold_font
        
        # 写入数据行
        for record in records:
            ws.append([
                record['id'],
                record['ip'],
                record['source_text'],
                record['translated_text'],
                record['source_lang'],
                record['target_lang'],
                record['timestamp'].strftime('%Y-%m-%d %H:%M:%S')
            ])
        
        # 调整列宽
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = (max_length + 2)
            ws.column_dimensions[column].width = adjusted_width
        
        # 创建内存中的Excel文件
        output = io.BytesIO()
        wb.save(output)
        output.seek(0)
        
        # 准备响应
        response = Response(
            output,
            mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response.headers["Content-Disposition"] = "attachment; filename=translation_records.xlsx"
        return response
        
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        connection.close()


@app.route("/export_user_history", methods=["GET"])
def export_user_history():
    """导出用户历史记录为Excel文件"""
    # 获取用户IP
    ip = request.headers.get('X-Forwarded-For', request.remote_addr)
    if ',' in ip:
        ip = ip.split(',')[0].strip()

    try:
        connection = get_db_connection()
        with connection.cursor() as cursor:
            # 获取该IP的所有翻译记录
            cursor.execute("""
                SELECT timestamp, source_text, translated_text 
                FROM translation_records 
                WHERE ip = %s 
                ORDER BY timestamp DESC
            """, (ip,))
            records = cursor.fetchall()
        
        if not records:
            return jsonify({"error": "没有历史记录可导出"}), 404

        # 创建Excel工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = "我的翻译历史"
        
        # 设置表头
        headers = ['时间', '原文', '译文']
        ws.append(headers)
        
        # 设置表头样式
        bold_font = Font(bold=True)
        for cell in ws[1]:
            cell.font = bold_font
        
        # 写入数据行
        for record in records:
            ws.append([
                record['timestamp'].strftime('%Y-%m-%d %H:%M:%S'),
                record['source_text'],
                record['translated_text']
            ])
        
        # 调整列宽
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = (max_length + 2)
            ws.column_dimensions[column].width = adjusted_width
        
        # 创建内存中的Excel文件
        output = io.BytesIO()
        wb.save(output)
        output.seek(0)
        
        # 准备响应
        response = Response(
            output,
            mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response.headers["Content-Disposition"] = "attachment; filename=my_translation_history.xlsx"
        return response
        
    except Exception as e:
        return jsonify({"error": str(e)}), 500
    finally:
        connection.close()
